package com.powerunion.datacollection.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import net.sf.json.JSONArray;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.powerunion.datacollection.common.Constants;
import com.powerunion.datacollection.common.SystemException;
import com.powerunion.datacollection.dao.DaoException;
import com.powerunion.datacollection.dao.TargetDao;
import com.powerunion.datacollection.model.Target;
import com.powerunion.datacollection.model.TargetDetails;
import com.powerunion.datacollection.model.jsonObject.TargetTreeNode;
import com.powerunion.datacollection.model.query.TargetQuery;
import com.powerunion.datacollection.persistence.db.jdbc.operator.QueryOperator;
import com.powerunion.datacollection.persistence.db.jdbc.operator.RsDataSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameter;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameterSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlType;
import com.powerunion.datacollection.persistence.db.jdbc.operator.UpdateOperator;
import com.powerunion.util.DBUtils;
import com.powerunion.util.log.LogHelper;
import com.powerunion.util.query.jdbc.QueryParameters;

public class TargetDaoImpl extends JdbcBase implements TargetDao  {
	// 初始化日志类
	private static final Logger logger = LogHelper.getInstance().getLogger(
			TargetDaoImpl.class);
	
	public boolean delete(String[] codes)throws DaoException {
		if (logger.isDebugEnabled()) {
			logger.debug(Constants.INTOMETHOD + "delete");
		}

		boolean flag = false;
		UpdateOperator update = new UpdateOperator();
		try{
			//删除对应的从表信息
			flag = this.deleteTargetDetails(codes);
			if(!flag){
				return false;
			}
			
			String sql = "delete from dc_target where id = ? and i_use = 0";
			update.setSql(sql);
			
			for(int i=0;i<codes.length;i++) {
				SqlParameterSet spSet = new SqlParameterSet();
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,codes[i]));
				update.addBatchParameter(spSet);
			}
			update.access();
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("删除指标失败！");
		}
		
		
		int[] effectCount = update.getBatchEffectCounts();
		if(effectCount != null && effectCount.length>0) {
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "指标信息"
						+ Constants.SUCCESS);
			}
			return true;
		}else{
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "指标信息"
						+ Constants.FAIL);
			}
			return false;
		}
	
	}
	
	public boolean deleteTargetDetails(String[] codes) throws DaoException {
		if (logger.isDebugEnabled()) {
			logger.debug(Constants.INTOMETHOD + "deleteTargetDetails");
		}

		String sql = "delete from dc_target_details where target_id=?";
		
		UpdateOperator update = new UpdateOperator();
		update.setSql(sql);
		//删除对应的从表信息
		for(int i=0;i<codes.length;i++) {
			SqlParameterSet spSet = new SqlParameterSet();
			spSet.addParameter(new SqlParameter(SqlType.VARCHAR,codes[i]));
			update.addBatchParameter(spSet);
		}
		try{
			update.access();
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("删除指标明细失败！");
		}
	
		
		int[] effectCount = update.getBatchEffectCounts();
		if(effectCount != null && effectCount.length>0) {
			if (logger.isDebugEnabled()) {
				logger.debug("deleteTargetDetails:" + Constants.DELETE + "指标信息明细"
						+ Constants.SUCCESS);
			}
			return true;
		}else{
			if (logger.isDebugEnabled()) {
				logger.debug("deleteTargetDetails:" + Constants.DELETE + "指标信息明细"
						+ Constants.FAIL);
			}
			return false;
		}
	
	}

	public String save(Target target) throws DaoException{
		if (logger.isDebugEnabled()) {
			logger.debug(Constants.INTOMETHOD + "save");
		}
		String sql = "";
		String resultId = "";
		if (target.getId() == null || target.getId().equals("")) {//新增
			String id = String.valueOf(this.getSeq("dc_target"));
			target.setId(id);
			StringBuffer sqlSave = new StringBuffer();
			sqlSave.append("insert into dc_target (target_name,units,periods,description,targets_category_id,")
				   .append("i_use,id) values(?,?,?,?,?,?,?)");
			sql = sqlSave.toString();
		} else {//修改
			StringBuffer sqlUpdate = new StringBuffer();
			sqlUpdate.append("update dc_target set target_name =?, units=?,periods=?,description=?,targets_category_id=?,")
			       .append("i_use=? where id =?");
			sql = sqlUpdate.toString();
		}
		Connection conn = null;
		UpdateOperator update = new UpdateOperator();
		try{
			conn = this.getConnection();
			
			update.setSql(sql);
			update.setPresentConnection(conn);
			update.setUseOnlyConnectionFlag(true);
			update.addParameter(new SqlParameter(SqlType.VARCHAR,target.getTargetName()));
			update.addParameter(new SqlParameter(SqlType.VARCHAR,target.getUnits()));
			update.addParameter(new SqlParameter(SqlType.VARCHAR,target.getPeriods()));
			update.addParameter(new SqlParameter(SqlType.VARCHAR,target.getDescription()));
			update.addParameter(new SqlParameter(SqlType.VARCHAR,target.getCategoryId()));
			update.addParameter(new SqlParameter(SqlType.INTEGER,new Integer(0))); 
			update.addParameter(new SqlParameter(SqlType.VARCHAR,target.getId()));
			update.access();
			
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("保存指标信息出错！");
		}finally{
			DBUtils.close(conn);
		}
	
		int effectCount = update.getEffectCounts();
		if(effectCount > 0) {
			this.saveTargetDetails(target.getTargetDetails(), target.getId(),conn);
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "指标信息"
						+ Constants.SUCCESS);
			}
			resultId = target.getId();
			return resultId;
		}else{
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "指标信息"
						+ Constants.FAIL);
			}
			return "";
		}
	}
	
	private boolean saveTargetDetails(List targetDetailsList,String targetId,Connection conn)
			throws DaoException{

		//参数为空
		if(StringUtils.isEmpty(targetId)) {
			String[] args = { "targetId" };
			// 打印错误日志
			StringBuffer msglog = new StringBuffer(
					"empty invoke parameter targetId NULL ");
			logger.error(msglog.toString());
			throw new SystemException("error.common.parameter.empty", args);
		}
		
		//参数为空
		if(targetDetailsList == null || targetDetailsList.size() == 0) {
			String[] args = { "targetDetails" };
			// 打印错误日志
			StringBuffer msglog = new StringBuffer(
					"empty invoke parameter targetDetails NULL ");
			logger.error(msglog.toString());
			throw new SystemException("error.common.parameter.empty", args);
		}
		
		//先删除原先对应从表中的明细信息
		String[] codes = new String[1];
		codes[0] = targetId;
		this.deleteTargetDetails(codes);
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("insert into dc_target_details(id,target_id,field_name,i_colletion_tartget,collect_type,description,i_computing_type,")
			  .append("computing_formula,displayOrder,scale,formula_RPolish,formula_condition,units) values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
		UpdateOperator update = new UpdateOperator();

	    try{
	    	update.setUseOnlyConnectionFlag(true);
	    	update.setPresentConnection(conn);
			update.setSql(sqlStr.toString());
			for(Iterator iter = targetDetailsList.iterator();iter.hasNext();){
				TargetDetails td = (TargetDetails)iter.next();
				SqlParameterSet spSet = new SqlParameterSet();
				String id = String.valueOf(this.getSeq("dc_target_details"));
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,id));
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,targetId));
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,td.getFieldName()));
				spSet.addParameter(new SqlParameter(SqlType.INTEGER,new Integer(td.getIcolletionTartget())));
				spSet.addParameter(new SqlParameter(SqlType.INTEGER,new Integer(td.getCollectType())));
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,td.getDescription()));
				spSet.addParameter(new SqlParameter(SqlType.INTEGER,new Integer(td.getIcomputingType())));
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,td.getComputingFormula()));
				spSet.addParameter(new SqlParameter(SqlType.INTEGER,new Integer(td.getDisplayOrder())));
				spSet.addParameter(new SqlParameter(SqlType.INTEGER,new Integer(td.getScale())));
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,td.getFormulaRPolish()));
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,td.getFormulaCondition()));
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,td.getUnits()));
		
				update.addBatchParameter(spSet);
			}
	    	update.access();
	    }catch(Exception e){
	    	if (logger.isDebugEnabled()) {
				logger.debug("saveTargetDetails:" + Constants.SAVE + "指标明细信息"
						+ Constants.FAIL);
			}
			throw new DaoException("保存指标明细信息出错！");
	    }
		
		int[] effectCount = update.getBatchEffectCounts();
		if(effectCount != null && effectCount.length>0) {
			if (logger.isDebugEnabled()) {
				logger.debug("saveTargetDetails:" + Constants.SAVE + "指标明细信息"
						+ Constants.SUCCESS);
			}
			return true;
		}else{
			if (logger.isDebugEnabled()) {
				logger.debug("saveTargetDetails:" + Constants.SAVE + "指标明细信息"
						+ Constants.FAIL);
			}
			return false;
		}
	
	}
	
	public Target getBykey(String id) {
		Target target = new Target();
		StringBuffer sql = new StringBuffer();
		sql.append("select id,target_name,units,periods,target_input_name,target_input_type,i_colletion_tartget,")
		   .append("collect_type,i_computing_type,computing_formula,description,targets_category_id from dc_target where id = ? ");
		QueryOperator query = new QueryOperator();
		query.setSql(sql.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR,id));
		query.access();
		if(query.resultSetAvailable()) {//检查结果集是否可用
			//获取并处理记录集
			RsDataSet rs = new RsDataSet(query.getSqlResultSet());
			if(rs.rowCount > 0) {
				target.setId(rs.getFieldValueAsString("id"));
				target.setTargetName(rs.getFieldValueAsString("target_name"));
				target.setUnits(rs.getFieldValueAsString("units"));
				target.setPeriods(rs.getFieldValueAsString("periods"));
				target.setIuse(rs.getFieldValueAsInteger("i_use").intValue());
				target.setDescription(rs.getFieldValueAsString("description"));
				target.setCategoryId(rs.getFieldValueAsString("targets_category_id"));
				
			}
			rs.clearAll();
		}
		return target;
	}
	public List findTargets(String categoryId) throws DaoException  {
		logger.debug(Constants.INTOMETHOD + "getTargets");
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select id,target_name,description,i_use,units,targets_category_id from dc_target ")
		   .append(" where targets_category_id = ? order by to_number(id)  ");
	
		String sql = sqlStr.toString();

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = this.getConnection();
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ps.setString(1, categoryId);
			rs = ps.executeQuery();
			
			List pageList = new ArrayList();
			while(rs.next()) {
				Target target = this.getTarget(rs);
				pageList.add(target);
			}

			return pageList;
			
		} catch (Exception e) {
			logger.error("getTargets:" + Constants.SEARCH
					+ "Targets" + Constants.FAIL,e);
			throw new SystemException("search db record error!");
		}finally{
			DBUtils.close(rs, ps, conn);
		}
	
	}
	
	public String getTargets(TargetQuery query,int start,int limit) {
		logger.debug(Constants.INTOMETHOD + "getTargets");
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select id,target_name,description,i_use,units,targets_category_id from dc_target ")
		   .append(" where targets_category_id = ? order by to_number(id) ");
	
		String sql = sqlStr.toString();

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = this.getConnection();
			//ResultSet.TYPE_SCROLL_INSENSITIVE:可滚动。但是不受其他用户对数据库更改的影响
			//ResultSet.CONCUR_READ_ONLY,只读
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ps.setMaxRows(start+limit);
			ps.setString(1, query.getCategoryId());
			rs = ps.executeQuery();
			//将游标移动到要输出的第一条记录
			rs.first();
			rs.relative(start-1);
			
			List pageList = new ArrayList();
			while(rs.next()) {
				Target target = this.getTarget(rs);
				pageList.add(target);
			}

			// 转化成JSON数组
			JSONArray jsonarr = JSONArray.fromObject(pageList);
			StringBuffer resultStr = new StringBuffer();
			int total = this.totalRecord(conn, query.getCategoryId());
			resultStr.append("{total:").append(total).append(",permission:'权限'").append(",results:")
					.append(jsonarr).append("}");
			logger.debug("getTargets:" + Constants.SEARCH + "targets"
					+ Constants.SUCCESS);
			return resultStr.toString();
			
		} catch (Exception e) {
			logger.error("getTargets:" + Constants.SEARCH
					+ "Targets" + Constants.FAIL,e);
			throw new SystemException("search db record error!");
		}finally{
			DBUtils.close(rs, ps, conn);
		}
	
	}
	
	private int totalRecord(Connection conn,String targets_category_id){
		int count=0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select count(id) from dc_target where targets_category_id = ? ";
			ps = conn.prepareStatement(sql);
			ps.setString(1, targets_category_id);
			rs = ps.executeQuery();
			while(rs.next()){
				count = rs.getInt(1);
			}
			
		}catch(Exception e){
			logger.error("totalRecord:" + Constants.SEARCH
					+ "targets totalRecord" + Constants.FAIL);
			logger.error("totalRecord() of throws : ", e);
		}finally{
			DBUtils.close(rs, ps);
		}
		return count;
	}
	
	public final Target getTarget(ResultSet rs) throws SQLException {
		Target target = new Target();

		target.setId(rs.getString("id"));
		target.setTargetName(rs.getString("target_name"));
		target.setDescription(rs.getString("description"));
		target.setCategoryId(rs.getString("targets_category_id"));
		target.setUnits(rs.getString("units"));
		target.setIuse(rs.getInt("i_use"));

		return target;
	}
	
	public List findTargetDetails(String targetId) throws DaoException {

		StringBuffer sql = new StringBuffer();
		sql.append("select field_name,i_colletion_tartget,collect_type,i_computing_type,computing_formula,scale,")
		   .append("target_input_type,description, displayOrder,formula_RPolish,formula_condition,units ")
		   .append(" from dc_target_details where target_id=? order by displayOrder desc");
		
		logger.debug(Constants.INTOMETHOD + "getTargetDetails");
		QueryParameters params = new QueryParameters();
		params.addParameter(targetId, Types.VARCHAR);
		List list = null;
		try {
			list = this.list(sql.toString(), params, "getTargetDetails");
		} catch (Exception e) {
			logger.error("findTargetDetails:" + Constants.SEARCH
					+ "targetDetails" + Constants.FAIL,e);
			throw new DaoException("search db record error!");
		}
		logger.debug("findTargetDetails" + Constants.SEARCH + "targetDetails"
				+ Constants.SUCCESS);
		return list;
		
	
	}

	public final TargetDetails getTargetDetails(ResultSet rs) throws SQLException {

		TargetDetails td = new TargetDetails();
		td.setTargetInputType(rs.getInt("target_input_type"));
		td.setComputingFormula(rs.getString("computing_formula"));
		td.setFieldName(rs.getString("field_name"));
		td.setIcolletionTartget(rs.getInt("i_colletion_tartget"));
		td.setIcomputingType(rs.getInt("i_computing_type"));
		td.setCollectType(rs.getInt("collect_type"));
		td.setDisplayOrder(rs.getInt("displayOrder"));
		td.setScale(rs.getInt("scale"));
		td.setDescription(rs.getString("description"));
		td.setFormulaRPolish(rs.getString("formula_RPolish"));
		td.setFormulaCondition(rs.getString("formula_condition"));
		td.setUnits(rs.getString("units"));
		
		return td;
	
	}

	public JSONArray getTargetsTree(String paraSeq) throws Exception {
		logger.info(Constants.INTOMETHOD + "getTargetsTree");
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List treeNodeArray = null;
		String sqlString = "select id,targets_category_id,target_name from dc_target where targets_category_id =" + paraSeq+" order by to_number(id) ";
				
		try {
			conn = this.getConnection();
			ps = conn.prepareStatement(sqlString);
			rs = ps.executeQuery();
			treeNodeArray = new ArrayList();

			while (rs.next()) {
					TargetTreeNode treeNode = new TargetTreeNode();
					treeNode.setId(rs.getString("id"));
					treeNode.setText(rs.getString("target_name"));
					treeNode.setExpandable(false);
					treeNode.setLeaf(true);
					treeNode.setType("2");
					
					List targetDetails = this.findTargetDetails(rs.getString("id"));
					treeNode.setTargetDetails(targetDetails);
					
					treeNodeArray.add(treeNode);
				}
			// 得到JSON数组
			JSONArray jsonarr = JSONArray.fromObject(treeNodeArray);
			logger.debug("===============treeNodeArray.size():"
					+ treeNodeArray.size() + "========================");
			logger.debug("===============targets tree:" + jsonarr
					+ "========================");
			logger.debug(Constants.SEARCH + "targets tree" + Constants.SUCCESS);
			// 返回JSON数据
			return jsonarr;
		} catch (Exception e) {
			logger.error("getTargetsTree() of throws : ", e);
			throw new Exception(Constants.SEARCH + "targets tree"
					+ Constants.FAIL);
		} finally {
			DBUtils.close(rs, ps, conn);
		}
	}
	
	public JSONArray getSelectTargetsTree(String paraSeq) throws Exception {
		logger.info(Constants.INTOMETHOD + "getSelectTargetsTree");
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List treeNodeArray = null;

		StringBuffer sqlString = new StringBuffer();
		sqlString.append("select id,targets_category_id,target_name from dc_target ")
		      .append("where targets_category_id =" + paraSeq).append(" order by to_number(id) ");
				
		try {
			conn = this.getConnection();
			ps = conn.prepareStatement(sqlString.toString());
			rs = ps.executeQuery();
			treeNodeArray = new ArrayList();

			while (rs.next()) {
					TargetTreeNode treeNode = new TargetTreeNode();
					treeNode.setId(rs.getString("id"));
					treeNode.setText(rs.getString("target_name"));
					treeNode.setExpandable(false);
				
					treeNode.setType("2");
					
					treeNodeArray.add(treeNode);
				}
			// 得到JSON数组
			JSONArray jsonarr = JSONArray.fromObject(treeNodeArray);
			logger.debug("===============treeNodeArray.size():"
					+ treeNodeArray.size() + "========================");
			logger.debug("===============targets tree:" + jsonarr
					+ "========================");
			logger.debug(Constants.SEARCH + "targets tree" + Constants.SUCCESS);
			// 返回JSON数据
			return jsonarr;
		} catch (Exception e) {
			logger.error("getSelectTargetsTree() of throws : ", e);
			throw new Exception(Constants.SEARCH + "targets tree"
					+ Constants.FAIL);
		} finally {
			DBUtils.close(rs, ps, conn);
		}
	}
	
	public JSONArray getTargetFieldTree(String paraSeq) throws Exception {
		logger.info(Constants.INTOMETHOD + "getTargetFieldTree");
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List treeNodeArray = null;
		String sqlString = "select id,field_name from dc_target_details where target_id =" + paraSeq;
				
		try {
			conn = this.getConnection();
			ps = conn.prepareStatement(sqlString);
			rs = ps.executeQuery();
			treeNodeArray = new ArrayList();

			while (rs.next()) {
					TargetTreeNode treeNode = new TargetTreeNode();
					treeNode.setId(rs.getString("id"));
					treeNode.setText(rs.getString("field_name"));
					treeNode.setExpandable(false);
					treeNode.setLeaf(true);
					treeNode.setType("3");
										
					if(treeNode.getText().indexOf("char") == -1){//过滤字符型自段
						treeNodeArray.add(treeNode);
					}
					
				}
			// 得到JSON数组
			JSONArray jsonarr = JSONArray.fromObject(treeNodeArray);
			logger.debug("===============treeNodeArray.size():"
					+ treeNodeArray.size() + "========================");
			logger.debug("===============targets tree:" + jsonarr
					+ "========================");
			logger.debug(Constants.SEARCH + "targets tree" + Constants.SUCCESS);
			// 返回JSON数据
			return jsonarr;
		} catch (Exception e) {
			logger.error("getTree() of throws : ", e);
			throw new Exception(Constants.SEARCH + "targets tree"
					+ Constants.FAIL);
		} finally {
			DBUtils.close(rs, ps, conn);
		}
	}
	
	
}
